from jqdatasdk import *
import pymysql
from tqdm import tqdm
import time
from datetime import datetime

# -------------function------------
def connect_db():
    db = pymysql.connect (user="root", password="sufeFin", host="127.0.0.1", database="sufeFin", port=8306)
    return db


def update_shareholder():
    select_sec_code = "select distinct secCode from reportInfo"
    with db.cursor () as cursor:
        cursor.execute (select_sec_code)
        sec_codes = cursor.fetchall ()

        for code in tqdm(sec_codes,desc="updating top10 shareholder"):
            time.sleep(0.4)
            code = code[0]
            top10_holder = finance.run_query (
                query (finance.STK_SHAREHOLDER_TOP10)
                    .filter (finance.STK_SHAREHOLDER_TOP10.code == code)
                    .order_by (finance.STK_SHAREHOLDER_TOP10.end_date.desc ())
                    .limit (10))
            top10_holder['secCode'] = [code]*10
            top10_holder = top10_holder[['id', 'company_name', 'company_id', 'code',
                                         'end_date', 'pub_date', 'change_reason_id',
                                         'change_reason', 'shareholder_rank',
                                         'shareholder_name', 'shareholder_name_en',
                                         'shareholder_id', 'shareholder_class_id',
                                         'shareholder_class', 'share_number',
                                         'share_ratio', 'sharesnature_id', 'sharesnature', 'secCode']].fillna(-1)
            tmp = top10_holder.to_numpy().tolist()
            tmp = [tuple(i for i in row) for row in tmp]
            try:
                cursor.executemany (
                    """insert ignore into top10_holder(id,company_name,company_id,code,end_date, pub_date, change_reason_id,
                change_reason, shareholder_rank,
                shareholder_name, shareholder_name_en,
                shareholder_id, shareholder_class_id,
                shareholder_class, share_number,
                share_ratio, sharesnature_id, sharesnature, secCode) 
                values ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",tmp)
                db.commit()
            except Exception as e:
                print(e)
                db.rollback()
                exit(-1)


# -------------准备数据-------------
# 获取接口授权
auth ('18687455266', '455266')
# 查询是否连接成功
is_auth = is_auth ()
print (f"是否登录成功:{is_auth}(同时只能登录三个账号),当日剩余调用次数{get_query_count ()}")

# 获取本地mysql连接
db = connect_db ()

# 获取shareholder内容
# update_shareholder()

# 获取行业信息
# 按照code获取申万一级行业的行业代码
secCode = "600519.XSHG"
now_date = datetime.now()
industry = get_industry(secCode,date=now_date)[secCode]['sw_l1']
code = industry['industry_code']
industry_name = industry['industry_name']
df=finance.run_query(query(finance.SW1_DAILY_PRICE).filter(finance.SW1_DAILY_PRICE.code==code,finance.SW1_DAILY_PRICE.date>'2019-01-01'))
print(df)
# 获取上游 获取同行关系 获取下游


# 登出
db.close()
logout ()
